淘宝用户行为分析

您所在的位置：网站首页 › tableau countd和count › 淘宝用户行为分析

淘宝用户行为分析

#淘宝用户行为分析| 来源: 网络整理| 查看: 265

一、项目背景

本项目以淘宝的真实用户的商品行为数据为基础，通过sql分析和tableau可视化，探索用户行为规律，寻找高价值用户；分析商品特征，寻找高贡献商品；分析产品功能，优化产品路径。

注：数据包含了抽样出来的1W用户在一个月时间（11.18~12.18）之内的移动端行为约一千两百万条数据。

二、分析流程

分析流程

主要从以下四个维度对用户行为进行分析和建议: 用户行为间的转化情况分析利用漏斗模型分析用户从商品浏览到购买整个过程中，常见的电商分析指标，确定各个环节流失率，提出改善转化率的建议。用户的行为习惯分析利用pv、uv等指标，找出用户活跃的日期以及每天的活跃时间段。用户类目偏好分析根据商品的点击、收藏、加购、购买频率，探索用户对商品的购买偏好，找到针对不同商品的营销策略(购买率较高的类目和产品，优化产品推荐)。用户价值分析找出最具有价值的核心用户群，针对这个群体推送个性化推送，优惠券或者活动。待续确认问题

本次分析的目的是想通过对用户行为数据进行分析，为以下问题提供解释和改进建议:

基于漏斗模型的用户购买流程各环节的分析指标，分析各环节的转化率，便于找到改进的环节。商品分析：找出热销商品，研究热销商品的特点。基于RFM模型找出核心付费用户群，对每部分群体做精准营销。三、数据读取

表结构

表结构说明

123456789use user_tb;create table user_action(user_id int (9),item_id int (9),behavior_type int (1),item_category int (5),time varchar (13));

数据导入

数据来源：阿里云-天池-淘宝用户购物行为数据可视化分析

四、数据预处理

数据预处理包括数据重复值、空缺值、异常值处理以及保持数据一致性。

数据重复值查看数据重复值 12345select *,count(1) from user_actiongroup by user_id,item_id,behavior_type,item_category,timehaving count(1)>1order by count(1) desc;

数据重复值

处理数据重复值

虽然可以看到有大量的重复数据。

但是通过实际分析，time的精确到小时，而一个小时内可以发生多个用户购买行为，所以忽略掉这些重复的数据。

数据空缺值

查看数据空缺值

12select count(user_id),count(item_id),count(behavior_type),count(item_category),count(time)from user_action;

数据空缺数值

数据异常值

从范围数值内入手或者是明显不符合实际逻辑的数据。

查看behavio type的类型

1select distinct behavior_type from user_action;

行为类型

四种行为，没有异常。

查看time的范围

1select max(time),min(time) from user_action;

时间范围

没有问题，符合数据集中的描述，时间是从2014年11月18号到2014年12月18号共一个月的时间。

数据一致性对time字段做处理：新建日期列，命名为date；新建小时列，命名为hour。更新新增列内容后删去原time列。 1234567alter table user_action add date date;update user_action set date=left(time,10);

alter table user_action add hour char(255);update user_action set hour=right(time,8);

alter table user_action drop time;

对behavior_type字段改为英文命名 123456789update user_actionset behavior_type=( case when behavior_type=1 then 'look' when behavior_type=2 then 'collect' when behavior_type=3 then 'cart' when behavior_type=4 then 'buy' else 'other' end ); 五、数据分析（一）整体情况概述1.月维度-总体情况 11月18日至12月18日总体uv、浏览总次数、人均浏览次数、收藏量、加购量、成交数 12345678910-- 整体create view month_all_cnt as -- 创建月维度-指标视图select count(distinct user_id) as uv,-- 独立访客数 sum(behavior_type='look')/count(distinct user_id) as per_view, -- 人均页面访问数 sum(behavior_type='look') as cnt_all_look,-- 浏览量 sum(behavior_type='collect') as cnt_all_collect,-- 收藏量 sum(behavior_type='cart') as cnt_all_cart,-- 加购量 sum(behavior_type='buy') as cnt_all_buy-- 成交量from user_action; 查询结果如下

month_all_cnt

可以看出分析的总用户数为1万人，一个月的订单量约为12万单，并且比起收藏，人们更倾向于加购。 2.DNU指标123456create view day_dnu as -- 创建每日新用户数视图select date,count(distinct user_id)from (select user_id,date,rank() over(partition by user_id order by date) r from user_action)t where r=1group by date; 查询结果如下

DNU指标

使用tableau将其可视化结果如下

图-DNU指标

可以看出DNU指标从首日便开始骤减，是由于数据是从11月18日开始，没有之前的数据源。故导致在11月18日中的“新增用户”极大部分是来自于此前活跃用户，并非当日新增的用户。

而次日到三日的DNU数据也出现一定程度的骤减情况，原因为这些“新增用户”也有部分来自于此前活跃用户。

越到后面越呈现稳定状态，也更能代表DNU真实情况。

3.整体行为数据（1）用户行为数据频次统计12345678910with view user_cnt_all as -- 用户维度整体情况select user_id, count(1) as all_cnt,-- 用户总点击量 sum(behavior_type='look') as cnt_all_look,-- 用户浏览量 sum(behavior_type='collect') as cnt_all_collect,-- 用户收藏量 sum(behavior_type='cart') as cnt_all_cart,-- 用户加购量 sum(behavior_type='buy') as cnt_all_buy-- 用户成交数from user_actiongroup by user_idorder by cnt_all_buy; 查询结果如下

user_cnt_all

统计出每个用户的总点击数、浏览数、收藏数、加购数和购买数，方便后续分析出用户的复购情况和浏览页面跳出情况。（2）用户整体复购情况12345with view user_again as -- 用户复购视图select sum(cnt_all_buy>0) as buy,-- 用户月买了多少次 sum(cnt_all_bu>1) as again_buy,-- 用户月复购多少次 concat(round((sum(cnt_all_bu>1)/sum(cnt_all_bu>0))*100,2),'%') as again_buy_rate-- 用户月复购率from user_cnt_all 查询结果如下

user_again

用户复购率高达91.69%，说明淘宝对用户有较大的吸引力使用户停留且用户的忠诚度非常高。可以进一步提高复购率，鼓励用户更高频次的购物。猜测其最大的原因是双十二当天的购买人数非常多，拉高了复购率。后续还要和别的月份相比较。（3）页面跳出情况浏览页面跳出12345678910111213create view user_lost as -- 用户跳出情况select count(distinct user_id) as user_lost, concat( round( (count(distinct user_id)/(select count(user_id) from user_cnt_all))*100 ,2) ,'%') as user_lost_ratefrom user_cnt_allwhere cnt_all_look>0 and cnt_all_collect=0 and cnt_all_cart=0 and cnt_all_cart=0 and cnt_all_buy=0 查询结果如下

user_lost

关键页面跳出指的是用户加购或者收藏却没有购买的行为 123456789101112create view user_keylost as -- 用户关键跳出情况select (select count(user_id) from user_cnt_all) as '总用户', count(distinct user_id) as '跳出用户', concat( round( (count(distinct user_id)/(select count(user_id) from user_cnt_all))*100 ,2) ,'%') as 关键跳出率from user_cnt_allwhere cnt_all_collect>0 or cnt_all_cart>0 and cnt_all_buy=0; 查询结果如下

user_keylost

跳出情况解读：

浏览页面跳出率为42.61%，关键页面跳出率我额70.29%。

分析：可能商品不符合用户需求，竞争力不够。

解决方法：可对比竞品网站，在商品的定价、商品陈列故事、商品详情描述等方面做出改进

4.用户留存情况12345678910111213141516171819create view user_remain as -- 创建用户留存表with temp_remain as ( select t1.date, sum(datediff(t2.date,t1.date)=0) remain_0,-- 当天用户数 sum(datediff(t2.date,t1.date)=1) remain_1,-- 第二天剩下来的用户数 sum(datediff(t2.date,t1.date)=2) remain_2,-- 第三天剩下来的用户数 sum(datediff(t2.date,t1.date)=6) remain_6-- 第七天剩下来的用户数 from (select user_id,date from user_action group by user_id,date)t1 left join (select user_id,date from user_action group by user_id,date)t2 on t1.user_id=t2.user_id and t1.date15天为1; 间隔在10-15天为2; 间隔在7-9天为3; 间隔在3-6天为4; 间隔在0-2天为5; 123456789101112131415161718-- 第一步：先将每个用户最近购买时间提取出来create table recency as -- 筛选出每个用户最近购买的日期select user_id,max(date) as recent_buy_timefrom user_action where behavior_type='buy'group by user_id;

-- 第二步：根据购买等级划分计算出得分create table r_value as select *, case when datediff('2014-12-18',recent_buy_time)>15 then 1 when datediff('2014-12-18',recent_buy_time) between 10 and 15 then 2 when datediff('2014-12-18',recent_buy_time)between 7 and 9 then 3 when datediff('2014-12-18',recent_buy_time)between 3 and 6 then 4 when datediff('2014-12-18',recent_buy_time)between 0 and 2 then 5 end as r -- 计算出每个用户的r得分情况from recency;

查询结果如下

r_value

F部分

将客户购买频次进行等级划分，越接近2014-12-18号R越大。

无购买，0分；1-49次，1分；50-99次，2分；100-299次，3分；300-499次，4分；大于500次，5分。

123456789101112-- 表user_cnt_all已经计算出每个用户购买的次数 -- 接下来根据购买频次等级计算出得分即可create table f_value as select user_id,cnt_all_buy, case when cnt_all_buy>500 then 5 when cnt_all_buy between 300 and 499 then 4 when cnt_all_buy between 100 and 299 then 3 when cnt_all_buy between 50 and 99 then 2 when cnt_all_buy between 1 and 49 then 1 else 0 end as f -- 计算出每个用户的r得分情况from user_cnt_all; 查询结果如下

f_value

用户划分由于只有R和F两个维度，所以分为以下四种，按照最近一次消费的均值和消费频率的均值定高低界限。重要高价值客户:指最近一次消费较近而且消费频率较高的客户; 重要唤回客户:指最近一次消费较远且消费频率较高的客户; 重要深耕客户:指最近一次消费较近且消费频率较低的客户; 重要挽留客户:指最近一次消费较远且消费频率较低的客户。计算出r和f的均值 123select avg(r) as r_avg,avg(f) as f_avgfrom r_value,f_valuewhere f0; -- 因为在划分的时候没有购买过的用户f值给0分，故计算平均得分时去掉这类用户查询结果如下

avg

r_avg=3.8522;f_avg=1.0396

用户划分成四类

123456789101112create table user_RF as select user_id, case when r>3.8522 and f>1.0396 then '重要高价值客户' when r1.0396 then '重要唤回客户' when r>3.8522 and f

【本文地址】

公司简介

联系我们